﻿Imports MySql.Data.MySqlClient
Public Class frmadd_ctndamage
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim idcontainer As String
    Dim respone As Object
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub frmadd_ctndamage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture

        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from ctnmain where CTNSTAT ='1';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else
                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If

                    .SubItems.Add(Format(mySqlReader("CTNVOYN"), "000"))
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else
                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add(mySqlReader("CTNMAINID"))
                End With





            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click
        idcontainer = ListView1.SelectedItems(0).SubItems(4).Text
    End Sub


    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click

        respone = MsgBox("ยืนยันข้อมูลถูกต้อง", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE ctnmain SET CTNSTAT = '0'   WHERE CTNMAINID = " & ListView1.SelectedItems(0).SubItems(4).Text & "; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into ctndepot ( CTNID, CTNSTATDEPOT, CTNDAYRE,CTNDEDATES,CTNSTATCHECK) values (@ctnid,@CTNSTATDEPOT,@ctndayre,@CTNDEDATES,@CTNSTATCHECK);  "
                mySqlCommand.Connection = mysql
                mySqlCommand.Parameters.AddWithValue("@ctnid", idcontainer)

                mySqlCommand.Parameters.AddWithValue("@CTNSTATDEPOT", "3")
                mySqlCommand.Parameters.AddWithValue("@ctndayre", "0")
                mySqlCommand.Parameters.AddWithValue("@CTNDEDATES", MaskedTextBoxAdv5.Text)
                mySqlCommand.Parameters.AddWithValue("@CTNSTATCHECK", "0")

                mySqlCommand.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

        End If

        searchCTN()
    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


        respone = MsgBox("ยืนยันข้อมูลถูกต้อง", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE ctnmain SET CTNSTAT = '0'   WHERE CTNMAINID = " & idcontainer & "; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into ctndepot ( CTNID, CTNDATEIN, CTNSTAT, CTNDAYRE) values (@ctnid,@ctndatein,@ctnstat,@ctndayre);  "
                mySqlCommand.Connection = mysql
                mySqlCommand.Parameters.AddWithValue("@ctnid", idcontainer)
                mySqlCommand.Parameters.AddWithValue("@ctndatein", "0")
                mySqlCommand.Parameters.AddWithValue("@ctnstat", "2")
                mySqlCommand.Parameters.AddWithValue("@ctndayre", "0")


                mySqlCommand.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()
            MsgBox("ย้ายตู้เสียจากลานมายัง Depot เรียบร้อยแล้วครับ ")
        End If

    End Sub

    Private Sub ButtonX3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX3.Click
        searchCTN()
    End Sub
    Public Sub searchCTN()
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from ctnmain where CTNSTAT ='1' and (CTNSTRING like '%" & TextBoxX1.Text & "%' or CTNVOYN like '%" & TextBoxX1.Text & "%' or CTNAGENT like '%" & TextBoxX1.Text & "%' );"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else
                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If

                    .SubItems.Add(Format(mySqlReader("CTNVOYN"), "000"))
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else
                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add(mySqlReader("CTNMAINID"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub TextBoxX1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX1.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchCTN()
        End If
    End Sub
End Class